/**
 * 
 */
package cn.edu.nju.ws.sview.wyge.relations.adataset.relations;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;

import cn.edu.nju.ws.sview.cache.UserNameIDCache;
import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.views.Group;
/**
 *
 * @author Weiyi Ge (geweiyi@gmail.com) 2012-8-6
 * 
 */
public class Relation implements Comparable<Relation>{
	private int relation_id;
	private String name;
//	private String user;
	private String group;//group name
	private int ov_id;//**********
	private String lang;
	private String date;
	private HashMap<String, String> variable_role;
	private Pattern pattern;
	private boolean isOriginal = false;//for original,if originalRelation set true

	public void delTriple(String s, int p, String o) {
		pattern.delTriple(s, p, o);
		List<String> variables = pattern.getVariables();
		if(variable_role.keySet().contains(s) && !(variables.contains(s))){
			variable_role.remove(s);
		}
		if(variable_role.keySet().contains(o) && !(variables.contains(o))){
			variable_role.remove(o);
		}
	}

	public String getVariableRole(String v) {
		if (variable_role.containsKey(v)) {
			return variable_role.get(v);
		}
		return "";
	}
	public HashMap<String, String> getVariableRole(){
		return this.variable_role;
	}
	
	public void setVariableRole(HashMap<String, String> vr) {
		this.variable_role = vr;
	}

	public void bindVariable(String v, String value) {
		pattern.bindVariable(v, value);
	}

	public int getRelationID() {
		return relation_id;
	}

	public String getName() {
		return name;
	}

//	public String getUser() {
//		return user;
//	}
	
	public String getGroup() {
		return group;
	}
	
	public int getOv_id() {
		return ov_id;
	}
	
	public String getLang() {
		return lang;
	}

	public String getDate() {
		return date;
	}

	public void setName(String name) {
		this.name = name;
	}

//	public void setUser(String user) {
//		this.user = user;
//	}
	
	public void setGroup(String group) {
		this.group = group;
	}

	public void setOv_id(int ov_id) {
		this.ov_id = ov_id;
	}
	
	public List<String> getVariables() {
		return pattern.getVariables();
	}

	public String toString() {
		String meta = name + ", " + group + ", " + lang + ", " + date;
		String pat = pattern.toString();
		return meta + "\r\n" + pat;
	}

	public List<String> getRoles() {
		List<String> roles = new ArrayList<String>();
		for (String v : getVariables()) {
			if (variable_role.containsKey(v)) {
				roles.add(variable_role.get(v));
			}
		}
		return roles;
	}

	public Pattern getPattern() {
		return pattern;
	}

	public void addTriple(String s, int p, String o) {
		pattern.addTriple(s, p, o);
	}
	//for originalRelation
	public Relation(int relation_id, String name, int group_id, int ov_id, String lang,
			String date, HashMap<String, String> variable_role, Pattern pattern, boolean isOriginal) {
		this.relation_id = relation_id;
		this.name = name;
		try {
			this.group = Group.getGroupNameById(group_id);
//			this.user = UserNameIDCache.getInstance().getName(user_id);
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.ov_id = ov_id;
		this.lang = lang;
		this.date = date;
		this.variable_role = variable_role;
		this.pattern = pattern;
		this.isOriginal = isOriginal;
	}
	public Relation(int relation_id, String name, int group_id, int ov_id, String lang,
			String date, HashMap<String, String> variable_role, Pattern pattern) {
		this.relation_id = relation_id;
		this.name = name;
		try {
			this.group = Group.getGroupNameById(group_id);
//			this.user = UserNameIDCache.getInstance().getName(user_id);
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.ov_id = ov_id;
		this.lang = lang;
		this.date = date;
		this.variable_role = variable_role;
		this.pattern = pattern;
	}

	public void serialize() {
		if(group==null){
			System.err.println("in Relation.java  serilize no group:"+group);
			return;
		}
		try {
			Connection conn = DBConnectionFactory.getConnection();
			conn.setAutoCommit(false);
			PreparedStatement insertrelps = conn
					.prepareStatement("insert relation (relation_id, name, group_id, ov_id, lang, time) values (?, ?, ?, ?, ?, now())");
			PreparedStatement insertpatternps = conn
					.prepareStatement("insert into re_pattern (relation_id, s, p, o) values (?, ?, ?, ?)");
			PreparedStatement insertroleps = conn
					.prepareStatement("insert into re_roles (relation_id, variable, role) values (?, ?, ?)");
			PreparedStatement delrelps = conn
					.prepareStatement("delete FROM relation where relation_id = ?");
			PreparedStatement delpatternps = conn
					.prepareStatement("delete FROM re_pattern where relation_id = ?");
			PreparedStatement delroleps = conn
					.prepareStatement("delete FROM re_roles where relation_id = ?");
			PreparedStatement insert2ps = conn
					.prepareStatement("insert into relation (name, group_id, ov_id, lang, time) values (?, ?, ?, ?, ?)");
			PreparedStatement selidps = conn
					.prepareStatement("SELECT relation_id FROM relation where name = ? and group_id = ? and ov_id=? and lang = ? and time = ?");

			 Statement stmt = conn.createStatement();
			if(ov_id == 0){
				stmt.executeUpdate("insert into overall_relation values();");
				ResultSet rs = stmt.executeQuery("select last_insert_id() id");
				conn.commit();
				if(rs.next()){
					ov_id = rs.getInt(1);
				}
			}
			if (ov_id != 0) {
				if(relation_id != 0){
					//del old infos
					delrelps.setInt(1, relation_id);
					delrelps.executeUpdate();
					delpatternps.setInt(1, relation_id);
					delpatternps.executeUpdate();
					delroleps.setInt(1, relation_id);
					delroleps.executeUpdate();
					conn.commit();
					
					//insert basic info
					insertrelps.setInt(1, relation_id);
					insertrelps.setString(2, name);
	//				insertrelps.setInt(3,
	//						UserNameIDCache.getInstance().getUserID(user));
					insertrelps.setInt(3, Group.getGroupIdByName(group));
					insertrelps.setInt(4, ov_id);
					insertrelps.setString(5, lang);
					insertrelps.executeUpdate();
				}else{// if(relation_id == 0) {
					Date now = new Date(new java.util.Date().getTime());
					insert2ps.setString(1, name);
//					insert2ps.setInt(2,
//							UserNameIDCache.getInstance().getUserID(user));
					insert2ps.setInt(2, Group.getGroupIdByName(group));
					insert2ps.setInt(3, ov_id);
					insert2ps.setString(4, lang);
					insert2ps.setDate(5, now);
					insert2ps.executeUpdate();

					selidps.setString(1, name);
//					selidps.setInt(2, UserNameIDCache.getInstance().getUserID(user));
					selidps.setInt(2, Group.getGroupIdByName(group));
					selidps.setInt(3, ov_id);
					selidps.setString(4, lang);
					selidps.setDate(5, now);
					ResultSet selidrs = selidps.executeQuery();
					if (selidrs.next()) {
						relation_id = selidrs.getInt(1);
					}
					conn.commit();
					selidrs.close();
				}
			}else{
				System.out.println("Relation: no ov_id error!");
			}			
			//insert patterns
			for (TriplePattern t : pattern.getTriples()) {
				String s = t.getSubject();
				int p = t.getProperty();
				String o = t.getObject();
				insertpatternps.setInt(1, relation_id);
				insertpatternps.setString(2, s);
				insertpatternps.setInt(3, p);
				insertpatternps.setString(4, o);
				insertpatternps.addBatch();
//				insertpatternps.executeUpdate();
			}
			insertpatternps.executeBatch();
			
			//insert roles
			for (Entry<String, String> entry : variable_role.entrySet()) {
				insertroleps.setInt(1, relation_id);
				insertroleps.setString(2, entry.getKey());
				insertroleps.setString(3, entry.getValue());
				insertroleps.addBatch();
//				insertroleps.executeUpdate();
			}
			insertroleps.executeBatch();
			conn.commit();
			
			conn.setAutoCommit(true);
			delroleps.close();
			delpatternps.close();
			delrelps.close();
			insertroleps.close();
			insertpatternps.close();
			insertrelps.close();
			insert2ps.close();
			selidps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Throwable e) {
			e.printStackTrace();
		} 
	}
	public static boolean isSameOV(int relationId1, int relationId2){
		boolean sameOv = false;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select null from relation x, relation y where x.ov_id=y.ov_id and x.relation_id=? and y.relation_id=?");
			pstmt.setInt(1, relationId1);
			pstmt.setInt(2, relationId2);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				sameOv = true;
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sameOv;
	}
	public static int getRelationIdByNameGroupClassID(String name, int groupID){
		int relationId = -1;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select relation_id from relation where name=? and group_id=?");
			pstmt.setString(1, name);
			pstmt.setInt(2, groupID);
//			System.out.println("name:"+name+"|group:"+groupID+"|stmt:"+pstmt.toString());			
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				relationId = rs.getInt(1);
//				System.out.println("relationId:"+relationId);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return relationId;
	}
	public static Relation getInstanceNew(int group_id) {
		Relation rel = null;
		HashMap<String, String> variable_role = new HashMap<String, String>();
		Pattern pattern = new Pattern();
		rel = new Relation(0, "", group_id, 0, "en", "", variable_role, pattern);
		return rel;
	}
	
	public static Relation getInstanceTranslate(Relation oldrel, String lang) {
		Relation rel = null;
		HashMap<String, String> variable_role = new HashMap<String, String>();//(HashMap<String, String>) oldrel.getVariableRole().clone();//
		Pattern pattern = oldrel.getPattern().clone();//new Pattern();
		rel = new Relation(0, "", Group.getGroupIdByName(oldrel.getGroup()), oldrel.getOv_id(), lang, "", variable_role, pattern);
		return rel;
	}
	public static String getLanguage(int relation_id){
		String lang = null;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("SELECT lang FROM relation where relation_id = ?");
			pstmt.setInt(1, relation_id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				lang = rs.getString(1);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lang;
	}
	public static Relation getInstance(int relation_id) {
		if (relation_id == 0) {
			return getInstanceNew(0);
		}
		Relation rel = null;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement selroleps = conn
					.prepareStatement("SELECT variable, role FROM re_roles where relation_id = ?");
			PreparedStatement selpatternps = conn
					.prepareStatement("SELECT s, p, o FROM re_pattern where relation_id = ?");
			PreparedStatement selps = conn
					.prepareStatement("SELECT name, group_id, ov_id, lang, time FROM relation where relation_id = ?");
			selps.setInt(1, relation_id);
			ResultSet selrs = selps.executeQuery();
			if (selrs.next()) {
				String name = selrs.getString(1);
				int group_id = selrs.getInt(2);
				int ov_id = selrs.getInt(3);
				String lang = selrs.getString(4);
				String time = new SimpleDateFormat("MM/dd, yyyy").format(selrs
						.getDate(5));
				selroleps.setInt(1, relation_id);
				ResultSet selrolers = selroleps.executeQuery();
				HashMap<String, String> variable_role = new HashMap<String, String>();
				while (selrolers.next()) {
					String variable = selrolers.getString(1);
					String role = selrolers.getString(2);
					variable_role.put(variable, role);
				}
				selrolers.close();
				Pattern pattern = new Pattern();
				selpatternps.setInt(1, relation_id);
				ResultSet selpatternrs = selpatternps.executeQuery();
				while (selpatternrs.next()) {
					String s = selpatternrs.getString(1);
					int p = selpatternrs.getInt(2);
					String o = selpatternrs.getString(3);
					pattern.addTriple(new TriplePattern(s, p, o));
				}
				selpatternrs.close();
				rel = new Relation(relation_id, name, group_id, ov_id, lang, time,
						variable_role, pattern);
			}
			selrs.close();

			selpatternps.close();
			selps.close();
			selroleps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rel;
	}
	public static void countUseTime(String ds_type, int ds_id, String user){
		
	}
	@Override
	public int compareTo(Relation o) {
		return o.getName().toLowerCase().compareTo(this.name.toLowerCase());
	}
}
